package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 退货报表
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/returnReportController")
public class ReturnReportController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(ReturnReportController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 退货明细报表 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "returnDetailsReportList")
	public ModelAndView returnDetailsReportList(HttpServletRequest request)
	{
          
		return new ModelAndView("yongxin/kingdeeReport/returnDetailsReportList");

	}
    
	/**
	 * 退货赔偿统计页面
	 * 
	 * @return
	 */
	@RequestMapping(params = "returnPayReportList")
	public ModelAndView returnPayReportList(HttpServletRequest request)
	{
          
		return new ModelAndView("yongxin/kingdeeReport/returnPayReportList");

	}
	
	
	/**
	 * 退货明细报表 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "returnPeichangReportList")
	public ModelAndView returnPeichangReportList(HttpServletRequest request)
	{
          
		return new ModelAndView("yongxin/kingdeeReport/returnPeichangReportList");

	}

	/**
	 * easyui 退货明细报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

        String FCustName = request.getParameter("FCustName");
        String Fdate_begin = request.getParameter("Fdate_begin");
        String Fdate_end = request.getParameter("Fdate_end");
        String FBatchNo = request.getParameter("FBatchNo");
        String FItemName = request.getParameter("FItemName");
        String FEntrySelfT0432 = request.getParameter("FEntrySelfT0432");
        String FEntrySelfT0437 = request.getParameter("FEntrySelfT0437");
		
	   String sql = " select v1.Fdate AS Fdate,t4.FName AS FCustName,t15.Fname AS FItemName,t15.Fmodel AS FItemModel,u1.FBatchNo AS FBatchNo,  ";
	   sql += " Convert(decimal(28,2),u1.FEntrySelfT0435) AS FEntrySelfT0435,u1.FEntrySelfT0436 AS FEntrySelfT0436,Convert(decimal(28,4),u1.Fauxqty) AS Fauxqty,  ";
	   sql += " u1.FEntrySelfT0432 AS FEntrySelfT0432,t1191.FName AS FEntrySelfT0437,u1.FEntrySelfT0433 AS FEntrySelfT0433  ";
	   sql += " from RemoteServer.AIS2009.dbo.QMSaleCkRequest v1 INNER JOIN RemoteServer.AIS2009.dbo.QMSaleCkRequestEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  "; 
	   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON   v1.FCustID = t4.FItemID  AND t4.FItemID<>0  "; 
	   sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t15 ON   u1.FItemID = t15.FItemID  AND t15.FItemID<>0  "; 
	   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t19 ON   t15.FUnitID = t19.FMeasureUnitID  AND t19.FMeasureUnitID<>0  "; 
	   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_SubMessage t1191 ON   u1.FEntrySelfT0437 = t1191.FInterID  AND t1191.FInterID<>0  ";
	   sql += " where 1=1 AND  (v1.FTranType=704 AND ( (v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";	
	   
	    if(StringUtil.isNotEmpty(FCustName)){
	      sql += " AND  t4.FName like '%" + FCustName + "%'";
	    }
	    if(StringUtil.isNotEmpty(Fdate_begin)){
		      sql += " AND  v1.Fdate  >= '" + Fdate_begin + "'";
		}
	    if(StringUtil.isNotEmpty(Fdate_end)){
		      sql += " AND  v1.Fdate  <= '" + Fdate_end + "'";
		}
	    if(StringUtil.isNotEmpty(FBatchNo)){
		      sql += " AND  u1.FBatchNo  like '%" + FBatchNo + "%'";
		}
	    if(StringUtil.isNotEmpty(FItemName)){
		      sql += " AND  t15.Fname  like '%" + FItemName + "%'";
		}
	    if(StringUtil.isNotEmpty(FEntrySelfT0432)){
		      sql += " AND  u1.FEntrySelfT0432  like '%" + FEntrySelfT0432 + "%'";
		}
	    if(StringUtil.isNotEmpty(FEntrySelfT0437)){
		      sql += " AND  t1191.FName  like '%" + FEntrySelfT0437 + "%'";
		}
	    
	    
		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){

					sql += " order by " + orderField + dataGrid.getOrder();

			}
		}

		List<Map<String, Object>> allmaplist = null;
		List<Map<String, Object>> maplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());
			
			
		} catch (Exception ex)
		{

		} finally
		{

		}
		
		
		
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		
		// 退货重量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		//BigDecimal amount = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("Fauxqty"));
		  // amount = amount.add((BigDecimal) map.get("Famount"));
		}
		dataGrid.setFooter("FCustName:合计," + "Fauxqty:" + sum_qty );
		
		TagUtil.datagrid(response, dataGrid);

	}
     
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
	     
		  String FCustName = request.getParameter("FCustName");
	        String Fdate_begin = request.getParameter("Fdate_begin");
	        String Fdate_end = request.getParameter("Fdate_end");
	        String FBatchNo = request.getParameter("FBatchNo");
	        String FItemName = request.getParameter("FItemName");
	        String FEntrySelfT0432 = request.getParameter("FEntrySelfT0432");
	        String FEntrySelfT0437 = request.getParameter("FEntrySelfT0437");
			
		   String sql = " select Convert(varchar(20),v1.Fdate) AS Fdate,t4.FName AS FCustName,t15.Fname AS FItemName,t15.Fmodel AS FItemModel,u1.FBatchNo AS FBatchNo,  ";
		   sql += " Convert(decimal(28,2),u1.FEntrySelfT0435) AS FEntrySelfT0435,u1.FEntrySelfT0436 AS FEntrySelfT0436,Convert(decimal(28,4),u1.Fauxqty) AS Fauxqty,  ";
		   sql += " u1.FEntrySelfT0432 AS FEntrySelfT0432,t1191.FName AS FEntrySelfT0437,u1.FEntrySelfT0433 AS FEntrySelfT0433  ";
		   sql += " from RemoteServer.AIS2009.dbo.QMSaleCkRequest v1 INNER JOIN RemoteServer.AIS2009.dbo.QMSaleCkRequestEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  "; 
		   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON   v1.FCustID = t4.FItemID  AND t4.FItemID<>0  "; 
		   sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t15 ON   u1.FItemID = t15.FItemID  AND t15.FItemID<>0  "; 
		   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t19 ON   t15.FUnitID = t19.FMeasureUnitID  AND t19.FMeasureUnitID<>0  "; 
		   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_SubMessage t1191 ON   u1.FEntrySelfT0437 = t1191.FInterID  AND t1191.FInterID<>0  ";
		   sql += " where 1=1 AND  (v1.FTranType=704 AND ( (v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";	
		   
		    if(StringUtil.isNotEmpty(FCustName)){
		      sql += " AND  t4.FName like '%" + FCustName + "%'";
		    }
		    if(StringUtil.isNotEmpty(Fdate_begin)){
			      sql += " AND  v1.Fdate  >= '" + Fdate_begin + "'";
			}
		    if(StringUtil.isNotEmpty(Fdate_end)){
			      sql += " AND  v1.Fdate  <= '" + Fdate_end + "'";
			}
		    if(StringUtil.isNotEmpty(FBatchNo)){
			      sql += " AND  u1.FBatchNo  like '%" + FBatchNo + "%'";
			}
		    if(StringUtil.isNotEmpty(FItemName)){
			      sql += " AND  t15.Fname  like '%" + FItemName + "%'";
			}
		    if(StringUtil.isNotEmpty(FEntrySelfT0432)){
			      sql += " AND  u1.FEntrySelfT0432  like '%" + FEntrySelfT0432 + "%'";
			}
		    if(StringUtil.isNotEmpty(FEntrySelfT0437)){
			      sql += " AND  t1191.FName  like '%" + FEntrySelfT0437 + "%'";
			}
		    
			// 排序
		/*	if(dataGrid.getSort() != null)
			{
				String orderField = dataGrid.getSort();
				if(dataGrid.getSort().equals(orderField)){
					if(dataGrid.getOrder() == SortDirection.asc){
						sql += " order by " + orderField + " asc";
					}else{
						sql += " order by " + orderField + " desc";
					}
				}
			}*/
		
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FCustName");
				arr[1] = (String) map.get("Fdate");
				arr[2] = (String) map.get("FBatchNo");
				arr[3] = (String) map.get("FItemName");
				arr[4] = (String) map.get("FItemModel");
				arr[5] =   String.valueOf((BigDecimal) map.get("FEntrySelfT0435"));
				arr[6] =  String.valueOf((BigDecimal) map.get("Fauxqty"));
				arr[7] = (String) map.get("FEntrySelfT0432");
				arr[8] = (String) map.get("FEntrySelfT0437");
				list.add(arr);
				 
			}
		
	
			String[] columnNames = new String[] { "退货单位", "退货日期", "批号", "物料名称","规格型号","宽度","退货重量(吨)","退货原因","处置判定"};
	       String titleName = "退货明细表.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	
	}
	
	
	
	/**
	 * easyu 退货赔偿统计表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid2")
	public void datagrid2(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

        String FCustName = request.getParameter("FCustName");
        String Fdate_begin = request.getParameter("Fdate_begin");
        String Fdate_end = request.getParameter("Fdate_end");
        
        String FEntrySelfT0432 = request.getParameter("FEntrySelfT0432");
        String FEntrySelfT0437 = request.getParameter("FEntrySelfT0437");
        String FName = request.getParameter("FName");
		
        
 String sql = " select t4.FName AS FCustName,Convert(decimal(28,4),sum(u1.Fauxqty)) AS sum_Fauxqty,t1191.FName AS FEntrySelfT0437,t15.FName, ";
		sql += " u1.FEntrySelfT0432 AS FEntrySelfT0432,sum(u1.FEntrySelfT0431) AS sum_FEntrySelfT0431,sum(u1.FEntrySelfT0434)  AS sum_FEntrySelfT0434 ";
		sql += " from RemoteServer.AIS2009.dbo.QMSaleCkRequest v1 INNER JOIN RemoteServer.AIS2009.dbo.QMSaleCkRequestEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0 "; 
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON   v1.FCustID = t4.FItemID  AND t4.FItemID<>0 "; 
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t15 ON   u1.FItemID = t15.FItemID  AND t15.FItemID<>0 "; 
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t19 ON   t15.FUnitID = t19.FMeasureUnitID  AND t19.FMeasureUnitID<>0 "; 
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_SubMessage t1191 ON   u1.FEntrySelfT0437 = t1191.FInterID  AND t1191.FInterID<>0  ";
		sql += " where 1=1 AND  (v1.FTranType=704 AND ( (v1.FCheckerID>0)  and   v1.FCancellation = 0)) "; 
		
	   
	    if(StringUtil.isNotEmpty(FCustName)){
	      sql += " AND  t4.FName like '%" + FCustName + "%'";
	    }
	    if(StringUtil.isNotEmpty(Fdate_begin)){
		      sql += " AND  v1.Fdate  >= '" + Fdate_begin + "'";
		}
	    if(StringUtil.isNotEmpty(Fdate_end)){
		      sql += " AND  v1.Fdate  <= '" + Fdate_end + "'";
		}
	    
	    if(StringUtil.isNotEmpty(FEntrySelfT0432)){
		      sql += " AND  u1.FEntrySelfT0432 like '%" + FEntrySelfT0432 + "%'";
		    }
	    if(StringUtil.isNotEmpty(FName)){
		      sql += " AND  t15.FName like '%" + FName + "%'";
		    }
	    if(StringUtil.isNotEmpty(FEntrySelfT0437)){
		      sql += " AND  t1191.FName like '%" + FEntrySelfT0437 + "%'";
		    }
	    
	    
	   sql += " group by t4.FName,t1191.FName,u1.FEntrySelfT0432,t15.FName ";
	

		List<Map<String, Object>> allmaplist = null;
		List<Map<String, Object>> maplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());
			
			
		} catch (Exception ex)
		{

		} finally
		{

		}
		
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		
		// 产品数量、赔偿金额、退货金额合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		BigDecimal amount = new BigDecimal("0.00");
		BigDecimal amount2 = new BigDecimal("0.00");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("sum_Fauxqty"));
		   amount = amount.add((BigDecimal) map.get("sum_FEntrySelfT0434"));
		   amount2 = amount2.add((BigDecimal) map.get("sum_FEntrySelfT0431"));
		}
		dataGrid.setFooter("FCustName:合计," + "sum_Fauxqty:" + sum_qty + ",sum_FEntrySelfT0434:" + amount+ ",sum_FEntrySelfT0431:" + amount2);
		
		TagUtil.datagrid(response, dataGrid);

	}
	
	
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export2")
	public void export2(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
	     
		   String FCustName = request.getParameter("FCustName");
	        String Fdate_begin = request.getParameter("Fdate_begin");
	        String Fdate_end = request.getParameter("Fdate_end");
	        
	        String FEntrySelfT0432 = request.getParameter("FEntrySelfT0432");
	        String FName = request.getParameter("FName");
			
	        
	 String sql = " select t4.FName AS FCustName,Convert(decimal(28,4),sum(u1.Fauxqty)) AS sum_Fauxqty,t1191.FName AS FEntrySelfT0437,t15.FName, ";
			sql += " u1.FEntrySelfT0432 AS FEntrySelfT0432,sum(u1.FEntrySelfT0431) AS sum_FEntrySelfT0431,sum(u1.FEntrySelfT0434)  AS sum_FEntrySelfT0434 ";
			sql += " from RemoteServer.AIS2009.dbo.QMSaleCkRequest v1 INNER JOIN RemoteServer.AIS2009.dbo.QMSaleCkRequestEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0 "; 
			sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON   v1.FCustID = t4.FItemID  AND t4.FItemID<>0 "; 
			sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t15 ON   u1.FItemID = t15.FItemID  AND t15.FItemID<>0 "; 
			sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t19 ON   t15.FUnitID = t19.FMeasureUnitID  AND t19.FMeasureUnitID<>0 "; 
			sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_SubMessage t1191 ON   u1.FEntrySelfT0437 = t1191.FInterID  AND t1191.FInterID<>0  ";
			sql += " where 1=1 AND  (v1.FTranType=704 AND ( (v1.FCheckerID>0)  and   v1.FCancellation = 0)) "; 
			
		   
		    if(StringUtil.isNotEmpty(FCustName)){
		      sql += " AND  t4.FName like '%" + FCustName + "%'";
		    }
		    if(StringUtil.isNotEmpty(Fdate_begin)){
			      sql += " AND  v1.Fdate  >= '" + Fdate_begin + "'";
			}
		    if(StringUtil.isNotEmpty(Fdate_end)){
			      sql += " AND  v1.Fdate  <= '" + Fdate_end + "'";
			}
		    
		    if(StringUtil.isNotEmpty(FEntrySelfT0432)){
			      sql += " AND  u1.FEntrySelfT0432 like '%" + FEntrySelfT0432 + "%'";
			    }
		    if(StringUtil.isNotEmpty(FName)){
			      sql += " AND  t15.FName like '%" + FName + "%'";
			    }
		    
		    
		    
		   sql += " group by t4.FName,t1191.FName,u1.FEntrySelfT0432,t15.FName ";
		
	   
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FCustName");
				arr[1] = String.valueOf((BigDecimal) map.get("sum_Fauxqty"));
				arr[2] = String.valueOf((BigDecimal) map.get("sum_FEntrySelfT0434"));
				arr[3] = String.valueOf((BigDecimal) map.get("sum_FEntrySelfT0431"));
				arr[4] = (String) map.get("FEntrySelfT0437");
				arr[5] = (String) map.get("FEntrySelfT0432");
				arr[6] = (String) map.get("FName");
				list.add(arr);
				 
			}
		
	
	
			String[] columnNames = new String[] {"客户名称", "产品数量", "赔偿金额","退货金额","处理判定","退货原因","物料名称"};
	       String titleName = "退货赔偿统计表.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	
	}

}
